package com.nf.dao.impl;

import com.nf.dao.ShowInfoDao;
import com.nf.entity.FilmInfo;
import com.nf.entity.ShowInfo;
import com.nf.util.DBUtil;
import com.nf.util.PagerUtil;
import com.nf.vo.ShowsVo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ShowInfoDaoImpl implements ShowInfoDao {
    @Override
    public List<ShowInfo> selectByShowDate(String showDate,int filmId) {
        List<ShowInfo> list=new ArrayList<ShowInfo>();

        Connection conn=DBUtil.getConnection();
        String sql="select * from showInfo where show_Date=? and film_id=?";

        PreparedStatement pstmt=null;
        ResultSet rst=null;
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,showDate);
            pstmt.setInt(2,filmId);

            rst=pstmt.executeQuery();
            while (rst.next()){
                int showId=rst.getInt(1);
                String filmName=rst.getString(2);
                String hallName=rst.getString(3);
                String showdate=rst.getString(4);
                String startTime=rst.getString(5);
                String endTime=rst.getString(6);
                Float price=rst.getFloat(7);
                int filmid=rst.getInt(8);
                int hallId=rst.getInt(9);

                ShowInfo show=new ShowInfo(showId,filmName,hallName,showdate,startTime,endTime,price,filmid,hallId);
                list.add(show);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(rst,pstmt,conn);
        }
        return list;
    }

    @Override
    public List<ShowsVo> selectAll() {
        List<ShowsVo> list=new ArrayList<ShowsVo>();

        Connection conn=DBUtil.getConnection();
        String sql="select s.show_id,s.film_name,s.hall_name,s.show_Date,s.start_time,s.end_time,s.price,s.film_id,s.hall_id,f.film_picture,h.hall_type from showInfo s,filmInfo f,hallInfo h where s.film_id=f.film_id and s.hall_id=h.hall_id order by s.show_id desc";

        PreparedStatement pstmt=null;
        ResultSet rst=null;
        try {
            pstmt=conn.prepareStatement(sql);

            rst=pstmt.executeQuery();
            while (rst.next()){
                int showId=rst.getInt(1);
                String filmName=rst.getString(2);
                String hallName=rst.getString(3);
                String showdate=rst.getString(4);
                String startTime=rst.getString(5);
                String endTime=rst.getString(6);
                Float price=rst.getFloat(7);
                int filmid=rst.getInt(8);
                int hallId=rst.getInt(9);
                String filmPicture=rst.getString(10);
                String hallType=rst.getString(11);

                ShowsVo show=new ShowsVo(showId,filmName,hallName,showdate,startTime,endTime,price,filmid,hallId,filmPicture,hallType);
                list.add(show);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(rst,pstmt,conn);
        }
        return list;
    }

    @Override
    public int delete(int showId) {
        int r=0;

        Connection conn=DBUtil.getConnection();
        String sql="delete from showInfo where show_id=?";
        PreparedStatement pstmt=null;
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,showId);
            r= pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(null,pstmt,conn);
        }

        return r;
    }

    @Override
    public int update(String showDate, String startTime, String endTime, float price, int showId) {
        int r=0;

        Connection conn=DBUtil.getConnection();
        String sql="update showInfo set show_Date=?,start_time=?,end_time=?,price=? where show_id=?";
        PreparedStatement pstmt=null;
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,showDate);
            pstmt.setString(2,startTime);
            pstmt.setString(3,endTime);
            pstmt.setFloat(4,price);
            pstmt.setInt(5,showId);

            r= pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(null,pstmt,conn);
        }
        return r;
    }

    @Override
    public int add(ShowInfo show) {
        int id=0;

        Connection conn=DBUtil.getConnection();
        String sql="insert into showInfo values(?,?,?,?,?,?,?,?)";
        PreparedStatement pstmt=null;
        ResultSet rst=null;
        try {
            pstmt=conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
            pstmt.setString(1, show.getFilmName());
            pstmt.setString(2, show.getHallName());
            pstmt.setString(3, show.getShowDate());
            pstmt.setString(4, show.getStartTime());
            pstmt.setString(5, show.getEndTime());
            pstmt.setFloat(6,show.getPrice());
            pstmt.setInt(7,show.getFilmId());
            pstmt.setInt(8,show.getHallId());

            pstmt.executeUpdate();
            rst= pstmt.getGeneratedKeys();
            if(rst.next()){
                id=rst.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(null,pstmt,conn);
        }

        return id;
    }

    @Override
    public List<ShowInfo> selectByHallId(int hallId) {
        List<ShowInfo> list=new ArrayList<>();

        Connection conn=DBUtil.getConnection();
        String sql="select * from showInfo where hall_id=?";

        PreparedStatement pstmt=null;
        ResultSet rst=null;
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,hallId);
            rst=pstmt.executeQuery();
            while (rst.next()){
                int showId=rst.getInt(1);
                String filmName=rst.getString(2);
                String hallName=rst.getString(3);
                String showdate=rst.getString(4);
                String startTime=rst.getString(5);
                String endTime=rst.getString(6);
                Float price=rst.getFloat(7);
                int filmid=rst.getInt(8);

                ShowInfo show=new ShowInfo(showId,filmName,hallName,showdate,startTime,endTime,price,filmid,hallId);
                list.add(show);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(rst,pstmt,conn);
        }

        return list;
    }

    @Override
    public PagerUtil<ShowInfo> selectAllByPager(int currentPage, int pageCount) {
        // 创建分页对象，并初始化分页的信息
        PagerUtil<ShowInfo> pager = new PagerUtil<ShowInfo>();

        // 封装总记录数
        pager.setTotalRecords(getCount());
        // 封装每页显示的记录数
        pager.setPageCount(pageCount);
        // 封装当前页
        pager.setCurrentPage(currentPage);

        List<ShowInfo> list =new  ArrayList<ShowInfo>();

        String sql = "select * from ( select *, row_number() over(order by a.show_id desc) as rownumber from ShowInfo as a) as b where rownumber between  ? and ?" ;
        Connection conn = DBUtil.getConnection();

        PreparedStatement pstmt = null ;
        ResultSet rst = null ;

        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, (currentPage - 1) * pageCount + 1);
            pstmt.setInt(2, currentPage * pageCount);
            rst = pstmt.executeQuery();

            while (rst.next()) {

                int showId=rst.getInt(1);
                String filmName=rst.getString(2);
                String hallName=rst.getString(3);
                String showdate=rst.getString(4);
                String startTime=rst.getString(5);
                String endTime=rst.getString(6);
                Float price=rst.getFloat(7);
                int filmid=rst.getInt(8);
                int hallId=rst.getInt(9);

                ShowInfo show=new ShowInfo(showId,filmName,hallName,showdate,startTime,endTime,price,filmid,hallId);
                list.add(show);
            }

            // 把当前页中的数据，封装起来
            pager.setData(list);

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DBUtil.close(rst, pstmt, conn);
        }

        return pager;
    }

    @Override
    public int getCount() {
        int r = 0 ;

        String sql ="select COUNT(show_id) from showInfo";
        Connection conn = DBUtil.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rst =null;
        try {
            pstmt = conn.prepareStatement(sql);
            rst = pstmt.executeQuery();
            if(rst.next()) {

                r=rst.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rst, pstmt, conn);
        }
        return r;
    }

    @Override
    public List<ShowInfo> selectByLike(String cond) {
        List<ShowInfo> list=new ArrayList<>();

        Connection conn=DBUtil.getConnection();
        String sql="select * from showInfo where film_name like ? or hall_name like ? or show_Date like ? or start_time like ?";
        PreparedStatement pstmt=null;
        ResultSet rst=null;
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,"%"+cond+"%");
            pstmt.setString(2,"%"+cond+"%");
            pstmt.setString(3,"%"+cond+"%");
            pstmt.setString(4,"%"+cond+"%");
            rst= pstmt.executeQuery();
            while (rst.next()){
                int showId=rst.getInt(1);
                String filmName=rst.getString(2);
                String hallName=rst.getString(3);
                String showdate=rst.getString(4);
                String startTime=rst.getString(5);
                String endTime=rst.getString(6);
                Float price=rst.getFloat(7);
                int filmid=rst.getInt(8);
                int hallId=rst.getInt(9);

                ShowInfo show=new ShowInfo(showId,filmName,hallName,showdate,startTime,endTime,price,filmid,hallId);
                list.add(show);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(rst,pstmt,conn);
        }
        return list;
    }
}
